﻿using Billing.Service.Billing;
using Billing.Service.Database;
using Helpers.Common;
using Helpers.Database;
using Helpers.Log;
using log4net;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;
using System.Web.Script.Serialization;

namespace Billing.Service.Wcf
{
    [ServiceBehavior(IncludeExceptionDetailInFaults = true, InstanceContextMode = InstanceContextMode.Single)]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class WcfService : IWcfService
    {
        #region Properties
        #endregion

        #region Fields

        private readonly MainDC mainDataContext;
        private Dictionary<string, Catalog> catalogsMeta = null;
        /// <summary>
        /// A <typeparamref name="log4net.ILog"/> object.
        /// </summary>
        private ILog logger = Helpers.Log.Logger.GetLogger(MethodBase.GetCurrentMethod().DeclaringType.Name);

        #endregion

        #region Static Fields

        private string DEFAULT_CON_STRING_NAME = "MainDb";

        #endregion

        #region Constructor

        public WcfService() 
        {
            this.mainDataContext = new MainDC(ConfigurationManager.ConnectionStrings[DEFAULT_CON_STRING_NAME].ConnectionString);
        }

        #endregion

        #region Wcf Methods

        public Response<int> AddCatalogItem(Dto.CatalogItem catItem)
        {
            TransactionLogger tranLog = new TransactionLogger(this.logger, MethodBase.GetCurrentMethod().Name);
            Response<int> response = null;
            try
            {
                tranLog.ParametersCollection = new Dictionary<string, object>()
                {
                    { "catItem", catItem },
                };

                //Log begin of the transaction.
                tranLog.LogBegin();


                string procedure = this.GetCatalogProcedure(catItem.CatalogName, CatalogProcedures.Insert, ref response);


                if (response == null)
                {
                    DataSet dataset;
                    List<SqlParameter> sqlParams = new List<SqlParameter>();

                    this.FillCollToSqlParams(catItem.Properties, sqlParams);

                    //Add output param for id field.
                    SqlParameter id = new SqlParameter("id", DbType.Int32);
                    id.Direction = ParameterDirection.Output;
                    sqlParams.Add(id);

                    tranLog.Log(string.Format("Procedure to execute: '{0}'", procedure));

                    //Execute procedure
                    dataset = HelperDatabase.ExecuteProcedure(procedure, sqlParams, (SqlConnection)this.mainDataContext.Connection);

                    //Set success response.
                    response = new Response<int>(true, Convert.ToInt32(id.Value));
                }

                //Log end of the transaction.
                tranLog.LogEnd(response);
            }
            catch (Exception ex)
            {
                string errorMsg = "Failed when trying to add item to catalog.";

                response = new Response<int>(ex, errorMsg);

                //Log end of the transaction with exception.
                tranLog.LogEnd(ex, errorMsg);
            }
            return response;
        }
        public Response<bool> AdjustProductsPrice(double adjIndex)
        {
            TransactionLogger tranLog = new TransactionLogger(this.logger, MethodBase.GetCurrentMethod().Name);
            Response<bool> response = null;
            try
            {
                tranLog.ParametersCollection = new Dictionary<string, object>()
                {
                    { "adjIndex", adjIndex },
                };

                //Log begin of the transaction.
                tranLog.LogBegin();

                List<SqlParameter> sqlParams = new List<SqlParameter>
                {
                    new SqlParameter("@index", adjIndex),
                };
                
                //Execute procedure
                HelperDatabase.ExecuteProcedure("CAT.AdjustProductsPrice", sqlParams, (SqlConnection)this.mainDataContext.Connection);

                //Set success response.
                response = new Response<bool>(true);

                //Log end of the transaction.
                tranLog.LogEnd(response);
            }
            catch (Exception ex)
            {
                string errorMsg = "Failed when adjust the price of all products.";

                if (ex is SqlException)
                    response = (new SqlResponse<bool>((SqlException)ex)).ToResponse();
                else
                    response = new Response<bool>(ex, errorMsg);

                //Log end of the transaction with exception.
                tranLog.LogEnd(ex, errorMsg);
            }
            return response;
        }
        public Response<List<Dictionary<string, object>>> GetCatalog(string catName)
        {
            TransactionLogger tranLog = new TransactionLogger(this.logger, MethodBase.GetCurrentMethod().Name);
            Response<List<Dictionary<string, object>>> response = null;
            try
            {
                tranLog.ParametersCollection = new Dictionary<string, object>()
                {
                    { "catName", catName },
                };

                //Log begin of the transaction.
                tranLog.LogBegin();


                string procedure = this.GetCatalogProcedure(catName, CatalogProcedures.Get, ref response);


                if (response == null)
                {
                    DataSet dataset;
                    List<Dictionary<string, object>> data;

                    tranLog.Log(string.Format("Procedure to execute: '{0}'", procedure));

                    //Execute procedure
                    dataset = HelperDatabase.ExecuteProcedure(procedure, (SqlConnection)this.mainDataContext.Connection);


                    //Parse dataset.
                    if ((dataset != null) && (dataset.Tables.Count > 0))
                    {
                        DataTable itemsDataTable = dataset.Tables[0];

                        DataColumn[] columns = itemsDataTable.Columns.Cast<DataColumn>()
                                                                        .ToArray();

                        data = itemsDataTable.Rows
                                .Cast<DataRow>()
                                /* Do not add DBNulls!! That breaks the service result serialization. Just replace them by common nulls. */
                                .Select(r => columns.ToDictionary(c => c.ColumnName, c => (r[c] != DBNull.Value) ? r[c] : null))
                                .ToList();
                    }
                    else
                    {
                        data = new List<Dictionary<string, object>>();
                    }


                    //Set success response.
                    response = new Response<List<Dictionary<string, object>>>(data);
                }

                //Log end of the transaction.
                tranLog.LogEnd(response);
            }
            catch (Exception ex)
            {
                string errorMsg = "Failed when trying to get catalog.";

                if (ex is SqlException)
                    response = (new SqlResponse<List<Dictionary<string, object>>>((SqlException)ex)).ToResponse();
                else
                    response = new Response<List<Dictionary<string, object>>>(ex, errorMsg);

                //Log end of the transaction with exception.
                tranLog.LogEnd(ex, errorMsg);
            }
            return response;
        }
        public Stream RunProcedure(ProcedureCall procCall)
        {
            TransactionLogger tranLog = new TransactionLogger(this.logger, MethodBase.GetCurrentMethod().Name);
            Stream st = null;
            try
            {
                Response<List<List<Dictionary<string, object>>>> response = null;

                try
                {
                    tranLog.ParametersCollection = new Dictionary<string, object>()
                    {
                        { "procCall", procCall },
                    };

                    //Log begin of the transaction.
                    tranLog.LogBegin();


                    DataSet dataset = this.ExecuteProcedure(procCall);


                    List<List<Dictionary<string, object>>> data = new List<List<Dictionary<string, object>>>();

                    if (dataset != null)
                    {
                        dataset.Tables
                            .Cast<DataTable>()
                            .ToList()
                            .ForEach(dataTable =>
                            {
                                DataColumn[] columns = dataTable.Columns
                                                            .Cast<DataColumn>()
                                                            .ToArray();

                                data.Add(
                                    dataTable.Rows
                                        .Cast<DataRow>()
                                        /* Do not add DBNulls!! That breaks the service result serialization. Just replace them by common nulls. */
                                        .Select(r => columns.ToDictionary(c => c.ColumnName, c => (r[c] != DBNull.Value) ? r[c] : null))
                                        .ToList()
                                );
                            });
                    }


                    //Set success response.
                    response = new Response<List<List<Dictionary<string, object>>>>(data);
                }
                catch (Exception ex)
                {
                    if (ex is SqlException)
                        response = (new SqlResponse<List<List<Dictionary<string, object>>>>((SqlException)ex)).ToResponse();
                    else
                        throw new Exception("Internal server error.", ex);
                }


                WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";

                st = new MemoryStream(
                    Encoding.UTF8.GetBytes(
                        new JavaScriptSerializer().Serialize(response.ToJsonableResponse())
                    )
                );


                //Log end of the transaction.
                tranLog.LogEnd(response);
            }
            catch (Exception ex)
            {
                string errorMsg = "Failed when trying to run procedure.";

                //Log end of the transaction with exception.
                tranLog.LogEnd(ex, errorMsg);
            }
            return st;
        }
        public Response<bool> UpdateCatalogItem(Dto.CatalogItem catItem)
        {
            TransactionLogger tranLog = new TransactionLogger(this.logger, MethodBase.GetCurrentMethod().Name);
            Response<bool> response = null;
            try
            {
                tranLog.ParametersCollection = new Dictionary<string, object>()
                {
                    { "catItem", catItem },
                };

                //Log begin of the transaction.
                tranLog.LogBegin();


                string procedure = this.GetCatalogProcedure(catItem.CatalogName, CatalogProcedures.Update, ref response);


                if (response == null)
                {
                    DataSet dataset;
                    List<SqlParameter> sqlParams = new List<SqlParameter>();

                    this.FillCollToSqlParams(catItem.Keys, sqlParams);
                    this.FillCollToSqlParams(catItem.Properties, sqlParams);

                    tranLog.Log(string.Format("Procedure to execute: '{0}'", procedure));

                    //Execute procedure
                    dataset = HelperDatabase.ExecuteProcedure(procedure, sqlParams, (SqlConnection)this.mainDataContext.Connection);

                    //Set success response.
                    response = new Response<bool>(true);
                }

                //Log end of the transaction.
                tranLog.LogEnd(response);
            }
            catch (Exception ex)
            {
                string errorMsg = "Failed when trying to add item to catalog.";

                if (ex is SqlException)
                    response = (new SqlResponse<bool>((SqlException)ex)).ToResponse();
                else
                    response = new Response<bool>(ex, errorMsg);

                //Log end of the transaction with exception.
                tranLog.LogEnd(ex, errorMsg);
            }
            return response;
        }

        #endregion

        #region Methods

        private DataSet ExecuteProcedure(ProcedureCall procCall)
        {
            DataSet dataset;
            ConnectionStringSettings conStringSetting = null;
            string conString = "";
            bool success = false;

            try
            {
                string conStringName = (string.IsNullOrWhiteSpace(procCall.ConnectionStringName) == true)
                                            ? DEFAULT_CON_STRING_NAME
                                            : procCall.ConnectionStringName;

                conStringSetting = ConfigurationManager.ConnectionStrings[conStringName];


                if (conStringSetting == null)
                    this.logger.Debug(string.Format("Connection string '{0}' does no exist.", conStringName));


                switch (procCall.Engine)
                {
                    case DatabaseEngine.SqlServer:
                        List<SqlParameter> sqlParams = new List<SqlParameter>();

                        foreach (QueryParameter param in procCall.Parameters)
                        {
                            SqlParameter sqlParam;

                            if (string.IsNullOrWhiteSpace(param.DbType) == true)
                            {
                                sqlParam = new SqlParameter(param.Name, param.Value);
                            }
                            else
                            {
                                SqlDbType dbType = HelperCommon.GetEnumByName<SqlDbType>(param.DbType);

                                sqlParam = new SqlParameter(param.Name, dbType);

                                if (dbType == SqlDbType.DateTime)
                                {
                                    if (string.IsNullOrWhiteSpace((string)param.Value) == true)
                                        sqlParam.Value = null;
                                    else
                                        sqlParam.Value = DateTime.Parse((string)param.Value, CultureInfo.GetCultureInfo(param.CultureInfoName));
                                }
                                else
                                {
                                    sqlParam.Value = param.Value;
                                }
                            }

                            sqlParams.Add(sqlParam);
                        }


                        //Blank InitialCatalog to be specified on ProcedureCall.
                        SqlConnectionStringBuilder conBuilder = new SqlConnectionStringBuilder(conStringSetting.ToString());
                        conBuilder.InitialCatalog = "";

                        conString = conBuilder.ConnectionString;

                        using (var con = new SqlConnection(conString))
                            dataset = HelperDatabase.ExecuteProcedure(procCall.Procedure, sqlParams, con);

                        success = true;

                        break;
                    default:
                        throw new ArgumentException("Unknown database engine.", "ProcedureCall.Engine");
                }
            }
            finally
            {
                if (success == false)
                {
                    if ((string.IsNullOrWhiteSpace(conString) == true) && (conStringSetting != null))
                        conString = conStringSetting.ToString();

                    this.logger.Warn(string.Format("Failed when trying to execute procedure. ConnString: '{0}'.", conString));
                }
            }

            return dataset;
        }
        private List<SqlParameter> FillCollToSqlParams(Dictionary<string, object> parameters)
        {
            return this.FillCollToSqlParams(parameters, new List<SqlParameter>());
        }
        private List<SqlParameter> FillCollToSqlParams(Dictionary<string, object> parameters, List<SqlParameter> procParams)
        {
            if (parameters != null)
            {
                parameters
                    .Select(kvp => kvp)
                    .ToList()
                    .ForEach(kvp => procParams.Add(new SqlParameter(kvp.Key, kvp.Value)));
            }

            return procParams;
        }
        private string GetCatalogProcedure<RType>(string catName, CatalogProcedures catProcType, ref Response<RType> response)
        {
            string procedure = null;

            Catalog catalog;
            string catNameUp;


            //Validate catName param.
            if ((response == null) && (string.IsNullOrWhiteSpace(catName) == true))
            {
                response = new Response<RType>(
                    string.Format(
                        "El argumento '{0}' no puede ser nulo o vacío.", "NombreCatálogo"
                    )
                );
            }


            //Fix catName to match with collection keys.
            catNameUp = catName.ToUpper();


            //Validate existence of catalog.
            if (response == null)
            {
                //Load catalogs metadata if is was not loaded yet.
                if (this.catalogsMeta == null)
                    this.LoadCatalogsMetadata();

                if (this.catalogsMeta.ContainsKey(catNameUp) == false)
                {
                    response = new Response<RType>(
                        string.Format("El catálogo '{0}' no existe.", catName)
                    );
                }
            }


            if (response == null)
            {
                //Get catalog meta info.
                catalog = this.catalogsMeta[catNameUp];

                switch (catProcType)
                {
                    case CatalogProcedures.Delete:
                        if (string.IsNullOrWhiteSpace(catalog.DeleteProcedure) == true)
                        {
                            procedure = string.Format("CAT.Delete{0}", catalog.Name);

                            this.logger.Debug("Use default procedure.");
                        }
                        else
                        {
                            procedure = catalog.DeleteProcedure;
                        }

                        break;
                    case CatalogProcedures.Get:
                        if (string.IsNullOrWhiteSpace(catalog.GetDataProcedure) == true)
                        {
                            procedure = string.Format("CAT.Get{0}s", catalog.Name);

                            this.logger.Debug("Use default procedure.");
                        }
                        else
                        {
                            procedure = catalog.GetDataProcedure;
                        }
                        break;
                    case CatalogProcedures.Insert:
                        if (string.IsNullOrWhiteSpace(catalog.InsertProcedure) == true)
                        {
                            procedure = string.Format("CAT.Insert{0}", catalog.Name);

                            this.logger.Debug("Use default procedure.");
                        }
                        else
                        {
                            procedure = catalog.InsertProcedure;
                        }
                        break;
                    case CatalogProcedures.Update:
                        if (string.IsNullOrWhiteSpace(catalog.UpdateProcedure) == true)
                        {
                            procedure = string.Format("CAT.Update{0}", catalog.Name);

                            this.logger.Debug("Use default procedure.");
                        }
                        else
                        {
                            procedure = catalog.UpdateProcedure;
                        }
                        break;
                    default:
                        throw new Exception("Invalid catalog procedure type.");
                }
            }


            //Validate catalog procedure.
            if ((response == null) && (string.IsNullOrWhiteSpace(procedure) == true))
            {
                response = new Response<RType>(
                    string.Format("El catálogo '{0}' está mal configurado.", catName)
                );
            }

            return procedure;
        }
        private void LoadCatalogsMetadata()
        {
            try
            {
                if (this.mainDataContext.Connection.State != ConnectionState.Open)
                {
                    try
                    {
                        this.logger.Debug("Connecting to billing database.");

                        this.mainDataContext.Connection.Open();

                        this.logger.Debug("Connected to billing database.");
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Failed when connecting to billing database.", ex);
                    }
                }



                this.catalogsMeta = this.mainDataContext.Catalogs.ToDictionary(item => item.Name.ToUpper());



                //Log catalogs info loaded.
                StringBuilder sb = new StringBuilder("Catalogs meta info loaded: [");

                foreach (KeyValuePair<string, Catalog> kvp in this.catalogsMeta)
                    sb.Append("[" + HelperCommon.ObjectToString(kvp.Value) + "]");

                sb.Append("]");

                this.logger.Debug(sb);
            }
            catch (Exception ex)
            {
                throw new Exception("Failed when getting data process status.", ex);
            }
        }

        #endregion
    }
}